﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.IO;

namespace BAL.EstRec.SQL
{
    public class ExecSQL
    {
        #region properties

        bool success;

        string scriptsPath;
        public string ScriptsPath
        {
            get { return scriptsPath; }
            set { scriptsPath = value; }
        }

        string cnnString;
        public string CnnString
        {
            get { return cnnString; }
            set { cnnString = value; }
        }

        string scriptsFailurePath;
        public string ScriptsFailurePath
        {
            get { return scriptsFailurePath; }
            set { scriptsFailurePath = value; }
        }

        string scriptsSuccessPath;
        public string ScriptsSuccessPath
        {
            get { return scriptsSuccessPath; }
            set { scriptsSuccessPath = value; }
        }


        #endregion
        public  Dictionary<string, string> ExecScripts()
        {
            string result = "";
            Dictionary<string, string> resultado = new Dictionary<string, string>();

            try
            {
                string[] listaScripts = ScriptsList(this.ScriptsPath);
                if (listaScripts.Length > 0)
                {
                    string nombre = "";
                    Array.Sort(listaScripts);
                    foreach (string script in listaScripts)
                    {
                        try
                        {
                            string[] n = script.Split('\\');
                            nombre = n[n.Length - 1];

                            Console.WriteLine("\n- Ejecutando Script: " + nombre);
                            execSQL(this.CnnString, script);
                            result += "Script ejecutado \n";
                        }
                        catch (Exception exe)
                        {
                            Console.WriteLine("\tNo se pudo completar la ejecución de " + nombre + ". " + exe.Message);
                            result += "\tNo se pudo completar la ejecución de " + nombre + ". " + exe.Message + "\n";
                        }

                    }//for each
                }
                else
                {
                    Console.WriteLine("\tNo se encontraron archivos en la ruta fuente \"" + this.ScriptsPath + "\"");
                    result += "\tNo se encontraron archivos en la ruta fuente \"" + this.ScriptsPath + "\" \n";
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                result += "Error en la ejecución de Scripts: " + ex.Message + "\n";
                resultado.Add("ERR", result);
            }
            resultado.Add("OK", result);

            return resultado;
        }//
        private string[] ScriptsList(string path)
        {
            try
            {
                string[] arr;

                if (Directory.Exists(path))
                {
                    arr = Directory.GetFiles(path);
                }
                else arr = new string[0];

                return arr;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error al recuperar scripts.");
                throw new Exception(ex.Message);
            }


        }//
        private void execSQL2(string cnn, string scriptName)
        {
            StreamReader sr = new StreamReader(scriptName);
            string txt = sr.ReadToEnd();
            IEnumerable<string> commandStrings = Regex.Split(txt, "^\\s*GO\\s*$", RegexOptions.Multiline);
        }
        public void execSQL(string cnn, string scriptName)
        {
            string connStr = cnn;
            FileInfo file;
            string script = "";
            string txt = "";

            try
            {
                //file = new FileInfo(scriptName); // (@"C:\Users\Inmeta\Downloads\test.sql");

                //if (file.Extension != ".sql")
                //{
                //    Console.WriteLine("No se puede ejecutar el archivo " + file.Name + " ya que no es de tipo sql.");
                //    return;
                //}

                //script = file.OpenText().ReadToEnd();
                StreamReader sr = new StreamReader(scriptName);
                txt = sr.ReadToEnd();
                sr.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
                
            }

            IEnumerable<string> commandStrings = Regex.Split(txt, "^\\s*GO\\s*$", RegexOptions.Multiline);
                
            //IEnumerable<string> commandStrings = Regex.Split(script, "^\\s*GO\\s*$", RegexOptions.Multiline);

            SqlConnection Connection = new SqlConnection(connStr);
            int rowsAffected = 0;
            try
            {
                Connection.Open();
                foreach (string commandString in commandStrings)
                {
                    Console.WriteLine(commandString);
                    if (commandString.Trim() != "")
                    {
                        SqlCommand cmd= new SqlCommand(commandString, Connection);
                        rowsAffected= cmd.ExecuteNonQuery();
                        this.success = true;
                    }
                }

                Connection.Close();

                Console.WriteLine("Script " + scriptName + " ejecutado!\tRows affected:" + rowsAffected);
                
            }
            catch (Exception ex)
            {
                this.success = false;
                Console.WriteLine("Error en la ejecución: " + ex.Message);

                MoveScripts(scriptName);
            }
            
            Connection.Dispose();
            MoveScripts(scriptName);

            //string dest = (this.success) ? this.scriptsSuccessPath : this.scriptsFailurePath; 
            //file.MoveTo(dest);
        }//end
        private void MoveScripts(string script)
        {
            string name = script.Split('\\').Last();
            try
            {
                if (this.success)
                {
                    Console.WriteLine("script {0} -- destino {1}" , script, this.scriptsSuccessPath+name);
                    File.Move(script, this.scriptsSuccessPath + name);

                }
                else
                {
                    Console.WriteLine("script {0} -- destino {1}" , script, this.scriptsFailurePath+name);
                    File.Move(script, this.scriptsFailurePath +name);
                }

            }
            catch (Exception ex)
            {
                //throw ex;
                Console.WriteLine("Error al mover script, se intentará renombrando...");
                try
                {
                    Random r = new Random(1);
                    int rnd = r.Next(0, 100);
                    string newName = name.Split('.')[0] + rnd.ToString() + '.' + name.Split('.').Last();

                    if (this.success)
                    {
                        Console.WriteLine("script {0} -- destino {1}", script, this.scriptsSuccessPath + newName);
                        File.Move(script, this.scriptsSuccessPath + newName);

                    }
                    else
                    {
                        Console.WriteLine("script {0} -- destino {1}", script, this.scriptsFailurePath + newName);
                        File.Move(script, this.scriptsFailurePath + newName);
                    }
                }
                catch (Exception exx)
                {
                    throw new Exception("Falló el copiado del archivo ejecutado. "+exx.Message+". "+exx.InnerException);
                }
            }
        
        }//e
        
    }// end class
}
